import json
import pandas as pd
from collections import defaultdict


def ocr_to_excel(json_path, output_file='output.xlsx', row_tolerance=5):
    """
    根据OCR坐标精确还原表格结构
    改进特性：
    1. 动态行检测代替固定行高
    2. 智能列合并处理
    3. 自动清理空白行列
    """
    # 读取JSON数据
    with open(json_path, 'r', encoding='utf-8') as f:
        data = json.load(f)

    # 数据结构: {row_key: {col_key: text}}
    table = defaultdict(lambda: defaultdict(str))

    # 坐标预处理：按Y轴为主键，X轴为次键排序
    sorted_items = sorted(data, key=lambda x: (x['position'][1], x['position'][0]))

    # 动态行检测
    current_row = []
    prev_y = None
    rows = []

    for item in sorted_items:
        y = item['position'][1]
        if prev_y is None or abs(y - prev_y) > row_tolerance:
            if current_row:
                rows.append(current_row)
            current_row = [item]
        else:
            current_row.append(item)
        prev_y = y
    if current_row:
        rows.append(current_row)

    # 构建表格结构
    for row_idx, row_items in enumerate(rows):
        # 按X坐标排序当前行元素
        sorted_cols = sorted(row_items, key=lambda x: x['position'][0])

        # 动态列检测
        prev_x = None
        current_col = 0

        for item in sorted_cols:
            x = item['position'][0]
            if prev_x is not None and (x - prev_x) > 50:  # 列间距阈值
                current_col += 1
            table[row_idx][current_col] = item['text']
            prev_x = x

    # 转换为DataFrame
    max_col = max((max(col.keys()) for col in table.values()), default=0)
    df_data = []
    for row in sorted(table.keys()):
        df_data.append([table[row].get(col, "") for col in range(max_col + 1)])

    # 创建DataFrame并清理空白
    df = pd.DataFrame(df_data)
    df = df.dropna(how='all').T.dropna(how='all').T  # 清理空行列

    # 保存Excel
    df.to_excel(output_file, index=False, header=False)
    print(f"表格已成功导出到: {output_file}")


# 使用示例
ocr_to_excel('C:/Users/zhuka/Downloads/00_rec.json', 'C:/Users/zhuka/Downloads/00_rec_3.xlsx')
